﻿using MySql.Data.MySqlClient;
using System.Collections.Generic;
using TShockAPI;
using TShockAPI.DB;

namespace BeanPoints
{
    internal class DBManager
    {
        public static void CreateTable() {
            SqlTable table = new SqlTable("bpplayers", new SqlColumn[] {
            new SqlColumn("ID",MySqlDbType.Int32){Primary=true },
            new SqlColumn("Name",MySqlDbType.VarChar,255){ Unique=true},
            new SqlColumn("Money",MySqlDbType.Int32),
            }) ;
            var writer = new SqlTableCreator(TShock.DB,TShock.DB.GetSqlType()==SqlType.Sqlite?(IQueryBuilder)new SqliteQueryCreator():new MysqlQueryCreator());
            writer.EnsureTableStructure(table);
        }
        public static BeanPlayer GetPlayer(string plrname) {
            string query = $"SELECT * FROM bpplayers WHERE Name='{plrname}';";
            using (var reader=TShock.DB.QueryReader(query)) {
                if (reader.Read())
                {
                    string name = reader.Get<string>("Name");
                    int id = reader.Get<int>("ID");
                    int points = reader.Get<int>("Money");
                    var plr = new BeanPlayer(id, name, points,TSPlayer.FindByNameOrID(name)[0]);
                    return plr;
                }
                else { return null; }
            }
        }
        public static BeanPlayer GetPlayerNoTP(string plrname)
        {
            string query = $"SELECT * FROM bpplayers WHERE Name='{plrname}';";
            using (var reader = TShock.DB.QueryReader(query))
            {
                if (reader.Read())
                {
                    string name = reader.Get<string>("Name");
                    int id = reader.Get<int>("ID");
                    int points = reader.Get<int>("Money");
                    var plr = new BeanPlayer(id, name, points);
                    return plr;
                }
                else { return null; }
            }
        }
        public static List<BeanPlayer> GetAllPlayers() {
            string query = $"SELECT * FROM bpplayers";
            using (var reader=TShock.DB.QueryReader(query)) 
            {
                List<BeanPlayer> players = new List<BeanPlayer>();
                while (reader.Read())
                {
                string name = reader.Get<string>("Name");
                int id = reader.Get<int>("ID");
                int points = reader.Get<int>("Money");
                var plr = new BeanPlayer(id, name, points);
                players.Add(plr);
                }
                return players;
            }
        }
        public static void AddPlayer(BeanPlayer plr) {
            string query = $"INSERT INTO bpplayers (ID,Name,Money) VALUES ({plr.ID},'{plr.Name}',{plr.Points})";
            TShock.DB.Query(query);
        }
        public static void DeletePlayer(BeanPlayer plr) {
            string query = $"DELETE FROM bpplayers WHERE Name='{plr.Name}';";
            TShock.DB.Query(query);
        }
        public static void UpdatePlayer(BeanPlayer plr) {
            string query = $"UPDATE bpplayers SET ID={plr.ID},Name='{plr.Name}',Money={plr.Points} WHERE Name='{plr.Name}';";
            TShock.DB.Query(query);
        }
        public static void SetPlayerMoney(string plrname,int count) {
            string query = $"UPDATE bpplayers SET Money={count} WHERE Name='{plrname}';";
            TShock.DB.Query(query);
        }
        public static int GetPlayerID(string plrname) {
            string query = $"SELECT * FROM bpplayers WHERE Name='{plrname}';";
            using (var reader=TShock.DB.QueryReader(query)) {
                int id;
                if (reader.Read())
                {
                    id = reader.Get<int>("ID");
                    return id;
                }
                else { return 0; }
            }
        }
    }
}
